package com.fenrirtec.aepp.common.dao.impl;

import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.condition.MemberCondition;
import com.fenrirtec.aepp.common.dao.MemberManageDao;
import com.fenrirtec.aepp.common.model.AuditFlow;
import com.fenrirtec.aepp.common.model.Member;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;
import com.fenrirtec.core.utils.DatabaseUtils;
import com.fenrirtec.core.utils.DigestUtils;

public class MemberManageDao_JDBCImpl implements MemberManageDao {

	private static final Logger logger = LoggerFactory.getLogger(MemberManageDao_JDBCImpl.class);

	@Override
	public Boolean regist(Member member) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#regist] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("insert into t_member ( \n")
				.append("  member_login_name, \n")
				.append("  member_login_password, \n")
				.append("  license_number, \n")
				.append("  enterprise_name, \n")
				.append("  enterprise_property, \n")
				.append("  enterprise_category, \n")
				.append("  contact_name, \n")
				.append("  contact_certificates_type, \n")
				.append("  contact_certificates_no, \n")
				.append("  area_code, \n")
				.append("  telephone, \n")
				.append("  mobile, \n")
				.append("  email, \n")
				.append("  qq_no, \n")
				.append("  weixin_no, \n")
				.append("  legal_name, \n")
				.append("  legal_certificates_type, \n")
				.append("  legal_certificates_no, \n")
				.append("  create_user, \n")
				.append("  update_user) \n")
				.append("values ( \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?) \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#regist] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, member.getMemberLoginName());
			statement.setString(2, DigestUtils.SHAHashing(member.getMemberLoginPassword()));
			statement.setString(3, member.getLicenseNumber());
			statement.setString(4, member.getEnterpriseName());
			statement.setInt(5, member.getEnterpriseProperty());
			statement.setInt(6, member.getEnterpriseCategory());
			statement.setString(7, member.getContactName());
			statement.setInt(8, member.getContactCertificatesType());
			statement.setString(9, member.getContactCertificatesNo());
			statement.setString(10, member.getAreaCode());
			statement.setString(11, member.getTelephone());
			statement.setString(12, member.getMobile());
			statement.setString(13, member.getEmail());
			statement.setString(14, member.getQqNo());
			statement.setString(15, member.getWeixinNo());
			statement.setString(16, member.getLegalName());
			if (member.getLegalCertificatesType() != null) {
				statement.setInt(17, member.getLegalCertificatesType());
			} else {
				statement.setNull(17, Types.NULL);
			}
			statement.setString(18, member.getLegalCertificatesNo());
			statement.setString(19, "system");
			statement.setString(20, "system");
			
			if (statement.executeUpdate() == 1) {
				return true;
			}
			
		} catch (SQLException | NoSuchAlgorithmException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#regist] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#regist] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
		return false;
	}

	@Override
	public Boolean exists(String memberLoginName) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#exists] start.");
		}

		PreparedStatement statement = null;
		
		try {
			String sql = "select count(*) as member_count from t_member where member_login_name = ?";
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#exists] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql);
			statement.setString(1, memberLoginName);
			ResultSet resultSet = statement.executeQuery();
			
			if (resultSet.next()) {
				return resultSet.getInt("member_count") > 0;
			}
			
			return false;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#exists] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#exists] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public List<Member> search(MemberCondition condition) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#search] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  member_login_name, \n")
				.append("  member_login_password, \n")
				.append("  license_number, \n")
				.append("  enterprise_name, \n")
				.append("  enterprise_property, \n")
				.append("  enterprise_property_name, \n")
				.append("  enterprise_category, \n")
				.append("  enterprise_category_name, \n")
				.append("  contact_name, \n")
				.append("  contact_certificates_type, \n")
                .append("  contact_certificates_type_name, \n")
				.append("  contact_certificates_no, \n")
				.append("  area_code, \n")
				.append("  telephone, \n")
				.append("  mobile, \n")
				.append("  mobile_certificated, \n")
				.append("  email, \n")
				.append("  email_certificated, \n")
				.append("  qq_no, \n")
				.append("  weixin_no, \n")
				.append("  legal_name, \n")
				.append("  legal_certificates_type, \n")
                .append("  legal_certificates_type_name, \n")
				.append("  legal_certificates_no, \n")
				.append("  image_contact_certificates_front, \n")
				.append("  image_contact_certificates_back, \n")
				.append("  image_legal_certificates_front, \n")
				.append("  image_legal_certificates_back, \n")
				.append("  image_license, \n")
				.append("  points, \n")
				.append("  rank, \n")
				.append("  rank_name, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag, \n")
				.append("  lastest_audit_sequence, \n")
				.append("  lastest_audit_date, \n")
				.append("  lastest_audit_phase, \n")
				.append("  lastest_audit_phase_name, \n")
				.append("  lastest_audit_result, \n")
				.append("  lastest_audit_result_name, \n")
				.append("  lastest_audit_comment, \n")
				.append("  lastest_audit_login_name \n")
				.append("from \n")
				.append("  v_member \n");
			
			StringBuilder sqlWhere = new StringBuilder();
			
			if (condition != null) {
				if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  member_login_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLicenseNumber())) {
					DatabaseUtils.appendCondition(sqlWhere, "  license_number = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getEnterpriseName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_name like ? \n");
				}
				if (condition.getEnterpriseProperty() != null && condition.getEnterpriseProperty() > 0) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_property = ? \n");
				}
				if (condition.getEnterpriseCategory() != null && condition.getEnterpriseCategory() > 0) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_category = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getContactName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  contact_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getContactCertificatesNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  contact_certificates_no = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getTelephone())) {
					DatabaseUtils.appendCondition(sqlWhere, "  telephone like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getMobile())) {
					DatabaseUtils.appendCondition(sqlWhere, "  mobile like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getEmail())) {
					DatabaseUtils.appendCondition(sqlWhere, "  email like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getQqNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  qq_no like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getWeixinNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  weixin_no = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLegalName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  legal_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLegalCertificatesNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  legal_certificates_no = ? \n");
				}
				if (condition.getPointsFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  points >= ? \n");
				}
				if (condition.getPointsTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  points <= ? \n");
				}
				if (condition.getRankFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  rank >= ? \n");
				}
				if (condition.getRankTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  rank <= ? \n");
				}
				if (condition.getAuditPhaseFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_phase >= ? \n");
				}
				if (condition.getAuditPhaseTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_phase <= ? \n");
				}
				if (condition.getAuditResultFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_result >= ? \n");
				}
				if (condition.getAuditResultTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_result <= ? \n");
				}
				if (condition.getCertificatesUploaded() != null) {
					if (condition.getCertificatesUploaded()) {
						DatabaseUtils.appendCondition(sqlWhere, "  not isnull(image_contact_certificates_front) \n");
						DatabaseUtils.appendCondition(sqlWhere, "  not isnull(image_contact_certificates_back) \n");
						DatabaseUtils.appendCondition(sqlWhere, "  not isnull(image_license) \n");
					} else {
						DatabaseUtils.appendCondition(sqlWhere, "  (isnull(image_contact_certificates_front) or isnull(image_contact_certificates_back) or isnull(image_license)) \n");
					}
				}
				DatabaseUtils.appendCondition(sqlWhere, "  delete_flag = false \n");
				if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
					sqlWhere.append("limit ?, ? \n");
				}
			}
			
			if (sqlWhere.length() > 0) {
				sql.append("where \n").append(sqlWhere);
			}
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#search] sql={}", sql.toString());
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			
			if (condition != null) {
				int index = 0;
				if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
					statement.setString(++index, "%" + condition.getMemberLoginName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLicenseNumber())) {
					statement.setString(++index, condition.getLicenseNumber());
				}
				if (StringUtils.isNotEmpty(condition.getEnterpriseName())) {
					statement.setString(++index, "%" + condition.getEnterpriseName() + "%");
				}
				if (condition.getEnterpriseProperty() != null && condition.getEnterpriseProperty() > 0) {
					statement.setInt(++index, condition.getEnterpriseProperty());
				}
				if (condition.getEnterpriseCategory() != null && condition.getEnterpriseCategory() > 0) {
					statement.setInt(++index, condition.getEnterpriseCategory());
				}
				if (StringUtils.isNotEmpty(condition.getContactName())) {
					statement.setString(++index, "%" + condition.getContactName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getContactCertificatesNo())) {
					statement.setString(++index, condition.getContactCertificatesNo());
				}
				if (StringUtils.isNotEmpty(condition.getTelephone())) {
					statement.setString(++index, "%" + condition.getTelephone() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getMobile())) {
					statement.setString(++index, "%" + condition.getMobile() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getEmail())) {
					statement.setString(++index, "%" + condition.getEmail() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getQqNo())) {
					statement.setString(++index, "%" + condition.getQqNo() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getWeixinNo())) {
					statement.setString(++index, "%" + condition.getWeixinNo() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLegalName())) {
					statement.setString(++index, "%" + condition.getLegalName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLegalCertificatesNo())) {
					statement.setString(++index, "%" + condition.getLegalCertificatesNo() + "%");
				}
				if (condition.getPointsFrom() != null) {
					statement.setInt(++index, condition.getPointsFrom());
				}
				if (condition.getPointsTo() != null) {
					statement.setInt(++index, condition.getPointsTo());
				}
				if (condition.getRankFrom() != null) {
					statement.setInt(++index, condition.getRankFrom());
				}
				if (condition.getRankTo() != null) {
					statement.setInt(++index, condition.getRankTo());
				}
				if (condition.getAuditPhaseFrom() != null) {
					statement.setInt(++index, condition.getAuditPhaseFrom());
				}
				if (condition.getAuditPhaseTo() != null) {
					statement.setInt(++index, condition.getAuditPhaseTo());
				}
				if (condition.getAuditResultFrom() != null) {
					statement.setInt(++index, condition.getAuditResultFrom());
				}
				if (condition.getAuditResultTo() != null) {
					statement.setInt(++index, condition.getAuditResultTo());
				}
				if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
					statement.setInt(++index, (condition.getPage() - 1) * condition.getRows());
					statement.setInt(++index, condition.getRows());
				}
			}
			
			ResultSet resultSet = statement.executeQuery();
			ResultSetMapper<Member> resultSetMapper = new ResultSetMapper<Member>();
			List<Member> members = resultSetMapper.mapResultSetToList(resultSet, Member.class);
			
			return members;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#search] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#search] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public Member info(String memberLoginName) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#info] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  member_login_name, \n")
				.append("  member_login_password, \n")
				.append("  license_number, \n")
				.append("  enterprise_name, \n")
				.append("  enterprise_property, \n")
				.append("  enterprise_property_name, \n")
				.append("  enterprise_category, \n")
				.append("  enterprise_category_name, \n")
				.append("  contact_name, \n")
				.append("  contact_certificates_type, \n")
                .append("  contact_certificates_type_name, \n")
				.append("  contact_certificates_no, \n")
				.append("  area_code, \n")
				.append("  telephone, \n")
				.append("  mobile, \n")
				.append("  mobile_certificated, \n")
				.append("  email, \n")
				.append("  email_certificated, \n")
				.append("  qq_no, \n")
				.append("  weixin_no, \n")
				.append("  legal_name, \n")
				.append("  legal_certificates_type, \n")
                .append("  legal_certificates_type_name, \n")
				.append("  legal_certificates_no, \n")
				.append("  image_contact_certificates_front, \n")
				.append("  image_contact_certificates_back, \n")
				.append("  image_legal_certificates_front, \n")
				.append("  image_legal_certificates_back, \n")
				.append("  image_license, \n")
				.append("  points, \n")
				.append("  rank, \n")
				.append("  rank_name, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag, \n")
				.append("  lastest_audit_sequence, \n")
				.append("  lastest_audit_date, \n")
				.append("  lastest_audit_phase, \n")
				.append("  lastest_audit_phase_name, \n")
				.append("  lastest_audit_result, \n")
				.append("  lastest_audit_result_name, \n")
				.append("  lastest_audit_comment, \n")
				.append("  lastest_audit_login_name \n")
				.append("from \n")
				.append("  v_member \n")
				.append("where \n")
				.append("  member_login_name = ? \n")
                .append("and \n")
                .append("  delete_flag = false \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#info] sql={}", sql.toString());
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, memberLoginName);
			ResultSet resultSet = statement.executeQuery();
			
			ResultSetMapper<Member> resultSetMapper = new ResultSetMapper<Member>();
			Member member = resultSetMapper.mapResultSetToObject(resultSet, Member.class);
			return member;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#info] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#info] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public Integer count(MemberCondition condition) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#count] start.");
		}

		PreparedStatement statement = null;
		Integer count = 0;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  count(*) as count \n")
				.append("from \n")
				.append("  v_member \n");
			
			StringBuilder sqlWhere = new StringBuilder();
			
			if (condition != null) {
				if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  member_login_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLicenseNumber())) {
					DatabaseUtils.appendCondition(sqlWhere, "  license_number = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getEnterpriseName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_name like ? \n");
				}
				if (condition.getEnterpriseProperty() != null && condition.getEnterpriseProperty() > 0) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_property = ? \n");
				}
				if (condition.getEnterpriseCategory() != null && condition.getEnterpriseCategory() > 0) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_category = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getContactName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  contact_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getContactCertificatesNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  contact_certificates_no = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getTelephone())) {
					DatabaseUtils.appendCondition(sqlWhere, "  telephone like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getMobile())) {
					DatabaseUtils.appendCondition(sqlWhere, "  mobile like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getEmail())) {
					DatabaseUtils.appendCondition(sqlWhere, "  email like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getQqNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  qq_no like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getWeixinNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  weixin_no = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLegalName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  legal_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLegalCertificatesNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  legal_certificates_no = ? \n");
				}
				if (condition.getPointsFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  points >= ? \n");
				}
				if (condition.getPointsTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  points <= ? \n");
				}
				if (condition.getRankFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  rank >= ? \n");
				}
				if (condition.getRankTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  rank <= ? \n");
				}
				if (condition.getAuditPhaseFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_phase >= ? \n");
				}
				if (condition.getAuditPhaseTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_phase <= ? \n");
				}
				if (condition.getAuditResultFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_result >= ? \n");
				}
				if (condition.getAuditResultTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  lastest_audit_result <= ? \n");
				}
				if (condition.getCertificatesUploaded() != null) {
					if (condition.getCertificatesUploaded()) {
						DatabaseUtils.appendCondition(sqlWhere, "  not isnull(image_contact_certificates_front) \n");
						DatabaseUtils.appendCondition(sqlWhere, "  not isnull(image_contact_certificates_back) \n");
						DatabaseUtils.appendCondition(sqlWhere, "  not isnull(image_license) \n");
					} else {
						DatabaseUtils.appendCondition(sqlWhere, "  (isnull(image_contact_certificates_front) or isnull(image_contact_certificates_back) or isnull(image_license)) \n");
					}
				}
				DatabaseUtils.appendCondition(sqlWhere, "  delete_flag = false \n");
			}
			
			if (sqlWhere.length() > 0) {
				sql.append("where \n").append(sqlWhere);
			}
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#count] sql={}", sql.toString());
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			
			if (condition != null) {
				int index = 0;
				if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
					statement.setString(++index, "%" + condition.getMemberLoginName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLicenseNumber())) {
					statement.setString(++index, condition.getLicenseNumber());
				}
				if (StringUtils.isNotEmpty(condition.getEnterpriseName())) {
					statement.setString(++index, "%" + condition.getEnterpriseName() + "%");
				}
				if (condition.getEnterpriseProperty() != null && condition.getEnterpriseProperty() > 0) {
					statement.setInt(++index, condition.getEnterpriseProperty());
				}
				if (condition.getEnterpriseCategory() != null && condition.getEnterpriseCategory() > 0) {
					statement.setInt(++index, condition.getEnterpriseCategory());
				}
				if (StringUtils.isNotEmpty(condition.getContactName())) {
					statement.setString(++index, "%" + condition.getContactName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getContactCertificatesNo())) {
					statement.setString(++index, condition.getContactCertificatesNo());
				}
				if (StringUtils.isNotEmpty(condition.getTelephone())) {
					statement.setString(++index, "%" + condition.getTelephone() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getMobile())) {
					statement.setString(++index, "%" + condition.getMobile() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getEmail())) {
					statement.setString(++index, "%" + condition.getEmail() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getQqNo())) {
					statement.setString(++index, "%" + condition.getQqNo() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getWeixinNo())) {
					statement.setString(++index, "%" + condition.getWeixinNo() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLegalName())) {
					statement.setString(++index, "%" + condition.getLegalName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLegalCertificatesNo())) {
					statement.setString(++index, "%" + condition.getLegalCertificatesNo() + "%");
				}
				if (condition.getPointsFrom() != null) {
					statement.setInt(++index, condition.getPointsFrom());
				}
				if (condition.getPointsTo() != null) {
					statement.setInt(++index, condition.getPointsTo());
				}
				if (condition.getRankFrom() != null) {
					statement.setInt(++index, condition.getRankFrom());
				}
				if (condition.getRankTo() != null) {
					statement.setInt(++index, condition.getRankTo());
				}
				if (condition.getAuditPhaseFrom() != null) {
					statement.setInt(++index, condition.getAuditPhaseFrom());
				}
				if (condition.getAuditPhaseTo() != null) {
					statement.setInt(++index, condition.getAuditPhaseTo());
				}
				if (condition.getAuditResultFrom() != null) {
					statement.setInt(++index, condition.getAuditResultFrom());
				}
				if (condition.getAuditResultTo() != null) {
					statement.setInt(++index, condition.getAuditResultTo());
				}
			}
			
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				count =  resultSet.getInt("count");
			}
			
			return count;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#count] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#count] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public void removeImage(String memberLoginName, Integer attachmentCategory, Integer attachmentId) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#removeImage] start.");
		}

		PreparedStatement statement = null;
		
		try {
			String updateColumn = "";
			switch (attachmentCategory) {
				case 1:
					updateColumn = "image_contact_certificates_front";
					break;
				case 2:
					updateColumn = "image_contact_certificates_back";
					break;
				case 3:
					updateColumn = "image_license";
					break;
				case 4:
					updateColumn = "image_legal_certificates_front";
					break;
				case 5:
					updateColumn = "image_legal_certificates_back";
					break;
			}
			StringBuilder sql = new StringBuilder()
			.append("update \n")
			.append("  t_member \n")
			.append("set \n")
			.append("  " + updateColumn + " = ? \n")
			.append("where \n")
			.append("  member_login_name = ? \n")
			.append("and \n")
			.append("  " + updateColumn + " = ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#removeImage] sql={}", sql.toString());
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setNull(1, Types.NULL);
			statement.setString(2, memberLoginName);
			statement.setLong(3, attachmentId);
			statement.executeUpdate();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#removeImage] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#removeImage] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public Boolean initAuditFlow(String memberLoginName) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#initAuditFlow] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("insert into t_audit_flow ( \n")
				.append("  member_login_name, \n")
				.append("  audit_sequence, \n")
				.append("  audit_phase, \n")
				.append("  audit_result, \n")
				.append("  login_name, \n")
				.append("  create_user) \n")
				.append("values ( \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?),( \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?),( \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?) \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#initAuditFlow] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, memberLoginName);
			statement.setInt(2, 1);
			statement.setInt(3, 1);
			statement.setInt(4, 9);
			statement.setString(5, memberLoginName);
			statement.setString(6, memberLoginName);
			statement.setString(7, memberLoginName);
			statement.setInt(8, 2);
			statement.setInt(9, 2);
			statement.setInt(10, 9);
			statement.setString(11, memberLoginName);
			statement.setString(12, memberLoginName);
			statement.setString(13, memberLoginName);
			statement.setInt(14, 3);
			statement.setInt(15, 3);
			statement.setInt(16, 1);
			statement.setString(17, memberLoginName);
			statement.setString(18, memberLoginName);
			
			statement.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#initAuditFlow] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#initAuditFlow] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public Boolean audit(String memberLoginName, Integer auditResult, String auditComment, String loginName) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#audit] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("insert into t_audit_flow ( \n")
				.append("  member_login_name, \n")
				.append("  audit_sequence, \n")
				.append("  audit_phase, \n")
				.append("  audit_result, \n")
				.append("  audit_comment, \n")
				.append("  login_name) \n")
				.append("values ( \n")
				.append("  ?, \n")
				.append("  get_lastest_audit_seq(?), \n")
				.append("  get_next_audit_phase(?, ?), \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?) \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#audit] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, memberLoginName);
			statement.setString(2, memberLoginName);
			statement.setString(3, memberLoginName);
			statement.setInt(4, auditResult);
			statement.setInt(5, auditResult);
			statement.setString(6, auditComment);
			statement.setString(7, loginName);
			
			if (statement.executeUpdate() == 1) {
				return true;
			}
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#audit] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#audit] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
		return false;
	}
	
	@Override
	public Boolean edit(Member member) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#edit] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("update t_member \n")
				.append("set \n")
				.append("  license_number = ?, \n")
				.append("  enterprise_name = ?, \n")
				.append("  enterprise_property = ?, \n")
				.append("  enterprise_category = ?, \n")
				.append("  contact_name = ?, \n")
				.append("  contact_certificates_type = ?, \n")
				.append("  contact_certificates_no = ?, \n")
				.append("  area_code = ?, \n")
				.append("  telephone = ?, \n")
				.append("  mobile = ?, \n")
				.append("  email = ?, \n")
				.append("  qq_no = ?, \n")
				.append("  weixin_no = ?, \n")
				.append("  legal_name = ?, \n")
				.append("  legal_certificates_type = ?, \n")
				.append("  legal_certificates_no = ?, \n")
				.append("  update_user = ?, \n")
				.append("  update_date = current_timestamp \n")
				.append("where \n")
				.append("  member_login_name = ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#edit] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, member.getLicenseNumber());
			statement.setString(2, member.getEnterpriseName());
			statement.setInt(3, member.getEnterpriseProperty());
			statement.setInt(4, member.getEnterpriseCategory());
			statement.setString(5, member.getContactName());
			statement.setInt(6, member.getContactCertificatesType());
			statement.setString(7, member.getContactCertificatesNo());
			statement.setString(8, member.getAreaCode());
			statement.setString(9, member.getTelephone());
			statement.setString(10, member.getMobile());
			statement.setString(11, member.getEmail());
			statement.setString(12, member.getQqNo());
			statement.setString(13, member.getWeixinNo());
			statement.setString(14, member.getLegalName());
			if (member.getLegalCertificatesType() != null) {
				statement.setInt(15, member.getLegalCertificatesType());
			} else {
				statement.setNull(15, Types.NULL);
			}
			statement.setString(16, member.getLegalCertificatesNo());
			statement.setString(17, member.getMemberLoginName());
			statement.setString(18, member.getMemberLoginName());
			
			if (statement.executeUpdate() == 1) {
				return true;
			}
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#edit] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#edit] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
		return false;
	}

	@Override
	public List<AuditFlow> auditFlowSearch(String memberLoginName) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#auditFlowSearch] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  member_login_name, \n")
				.append("  audit_sequence, \n")
				.append("  audit_date, \n")
				.append("  audit_phase, \n")
				.append("  audit_result, \n")
				.append("  audit_comment, \n")
				.append("  login_name, \n")
				.append("  audit_phase_name, \n")
				.append("  audit_result_name, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag \n")
				.append("from \n")
				.append("  v_audit_flow \n")
				.append("where \n")
				.append("  member_login_name = ? \n")
				.append("order by \n")
				.append("  audit_sequence desc \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#auditFlowSearch] sql={}", sql.toString());
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, memberLoginName);
			
			ResultSet resultSet = statement.executeQuery();
			ResultSetMapper<AuditFlow> resultSetMapper = new ResultSetMapper<AuditFlow>();
			List<AuditFlow> auditFlowList = resultSetMapper.mapResultSetToList(resultSet, AuditFlow.class);
			
			return auditFlowList;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#auditFlowSearch] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#auditFlowSearch] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public void updateAuditResult(AuditFlow auditFlow) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#updateAuditResult] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("update t_audit_flow \n")
				.append("set \n")
				.append("  audit_date = current_timestamp, \n")
				.append("  audit_result = ?, \n")
				.append("  audit_comment = ?, \n")
				.append("  login_name = ?, \n")
				.append("  update_user = ?, \n")
				.append("  update_date = current_timestamp \n")
				.append("where \n")
				.append("  member_login_name = ? \n")
				.append("and \n")
				.append("  audit_sequence = ? \n")
				.append("and \n")
				.append("  audit_phase = ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#updateAuditResult] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, auditFlow.getAuditResult());
			statement.setString(2, auditFlow.getAuditComment());
			statement.setString(3, auditFlow.getLoginName());
			statement.setString(4, auditFlow.getUpdateUser());
			statement.setString(5, auditFlow.getMemberLoginName());
			statement.setInt(6, auditFlow.getAuditSequence());
			statement.setInt(7, auditFlow.getAuditPhase());
			
			statement.executeUpdate();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#updateAuditResult] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#updateAuditResult] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public void insertAuditFlow(AuditFlow auditFlow) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#insertAuditFlow] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("insert into t_audit_flow ( \n")
				.append("  member_login_name, \n")
				.append("  audit_sequence, \n")
				.append("  audit_phase, \n")
				.append("  audit_result, \n")
				.append("  login_name, \n")
				.append("  create_user) \n")
				.append("values ( \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?, \n")
				.append("  ?) \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#insertAuditFlow] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, auditFlow.getMemberLoginName());
			statement.setInt(2, auditFlow.getAuditSequence());
			statement.setInt(3, auditFlow.getAuditPhase());
			statement.setInt(4, auditFlow.getAuditResult());
			statement.setString(5, auditFlow.getMemberLoginName());
			statement.setString(6, auditFlow.getMemberLoginName());
			
			statement.executeUpdate();
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#insertAuditFlow] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#insertAuditFlow] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

    @Override
    public Boolean deleteAuditFlow(String memberLoginName) {
        if (logger.isInfoEnabled()) {
            logger.info("[MemberManageDao_JDBCImpl#deleteAuditFlow] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("delete from t_audit_flow \n")
                .append("where \n")
                .append("  member_login_name = ? \n");
            
            if (logger.isTraceEnabled()) {
                logger.trace("[MemberManageDao_JDBCImpl#deleteAuditFlow] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, memberLoginName);
            int count = statement.executeUpdate();
            if (count > 0) {
                result = true;
            }
            return result;
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[MemberManageDao_JDBCImpl#deleteAuditFlow] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[MemberManageDao_JDBCImpl#deleteAuditFlow] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }

	@Override
	public List<Member> findAll() {
		 if (logger.isInfoEnabled()) {
	            logger.info("[MemberManageDao_JDBCImpl#findAll] start.");
	        }

	        PreparedStatement statement = null;
	        try {
	            StringBuilder sql = new StringBuilder()
	                .append("select \n")
	                .append("  member_login_name, \n")
	                .append("  enterprise_name, \n")
	                .append("  enterprise_property_name, \n")
	                .append("  enterprise_category_name, \n")
	                .append("  contact_name, \n")
	                .append("  contact_certificates_type, \n")
	                .append("  contact_certificates_no, \n")
	                .append("  area_code, \n")
	                .append("  telephone, \n")
	                .append("  mobile, \n")
	                .append("  mobile_certificated, \n")
	                .append("  email, \n")
	                .append("  email_certificated, \n")
	                .append("  qq_no, \n")
	                .append("  weixin_no, \n")
	                .append("  legal_name, \n")
	                .append("  legal_certificates_type, \n")
	                .append("  legal_certificates_no, \n")
	                .append("  points, \n")
	                .append("  rank_name \n")	               
	                .append("from \n")
	                .append("  v_member \n")
	                .append("where \n")
	                .append("  delete_flag = false \n");
	            if (logger.isTraceEnabled()) {
	                logger.trace("[MemberManageDao_JDBCImpl#findAll] sql={}", sql);
	            }
	            Connection connection = DatabaseSessionManager.getInstance().getSession();
	            statement = connection.prepareStatement(sql.toString());
	            ResultSet resultSet = statement.executeQuery();
	            
	            ResultSetMapper<Member> resultSetMapper = new ResultSetMapper<Member>();
	            List<Member> memberList = resultSetMapper.mapResultSetToList(resultSet, Member.class);
	            return memberList;
	        } catch (SQLException e) {
	            if (logger.isTraceEnabled()) {
	                logger.trace("[MemberManageDao_JDBCImpl#findAll] sql error occurred.", e);
	            }
	            throw new DatabaseException("database error occurred.", e);
	        } finally {
	            if (logger.isInfoEnabled()) {
	                logger.info("[MemberManageDao_JDBCImpl#findAll] finish.");
	            }
	            if (statement != null) { 
	            	try { statement.close(); 
	            	} catch (SQLException e) {
	            		
	            	}	            	
	            }
	        }
	}

	@Override
	public List<Member> memberSearch(MemberCondition condition) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#memberSearch] start.");
		}

		PreparedStatement statement = null;
		
		try {
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  member_login_name, \n")
				.append("  member_login_password, \n")
				.append("  license_number, \n")
				.append("  enterprise_name, \n")
				.append("  enterprise_property, \n")
				.append("  enterprise_property_name, \n")
				.append("  enterprise_category, \n")
				.append("  enterprise_category_name, \n")
				.append("  contact_name, \n")
				.append("  contact_certificates_type, \n")
                .append("  contact_certificates_type_name, \n")
				.append("  contact_certificates_no, \n")
				.append("  area_code, \n")
				.append("  telephone, \n")
				.append("  mobile, \n")
				.append("  mobile_certificated, \n")
				.append("  email, \n")
				.append("  email_certificated, \n")
				.append("  qq_no, \n")
				.append("  weixin_no, \n")
				.append("  legal_name, \n")
				.append("  legal_certificates_type, \n")
                .append("  legal_certificates_type_name, \n")
				.append("  legal_certificates_no, \n")
				.append("  image_contact_certificates_front, \n")
				.append("  image_contact_certificates_back, \n")
				.append("  image_legal_certificates_front, \n")
				.append("  image_legal_certificates_back, \n")
				.append("  image_license, \n")
				.append("  points, \n")
				.append("  rank, \n")
				.append("  rank_name, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag \n")				
				.append("from \n")
				.append("  v_member \n");
			
			StringBuilder sqlWhere = new StringBuilder();
			
			if (condition != null) {
				if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  member_login_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLicenseNumber())) {
					DatabaseUtils.appendCondition(sqlWhere, "  license_number = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getEnterpriseName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_name like ? \n");
				}
				if (condition.getEnterpriseProperty() != null && condition.getEnterpriseProperty() > 0) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_property = ? \n");
				}
				if (condition.getEnterpriseCategory() != null && condition.getEnterpriseCategory() > 0) {
					DatabaseUtils.appendCondition(sqlWhere, "  enterprise_category = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getContactName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  contact_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getContactCertificatesNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  contact_certificates_no = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getAreaCode())) {
					DatabaseUtils.appendCondition(sqlWhere, "  area_code like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getTelephone())) {
					DatabaseUtils.appendCondition(sqlWhere, "  telephone like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getMobile())) {
					DatabaseUtils.appendCondition(sqlWhere, "  mobile like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getEmail())) {
					DatabaseUtils.appendCondition(sqlWhere, "  email like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getQqNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  qq_no like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getWeixinNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  weixin_no = ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLegalName())) {
					DatabaseUtils.appendCondition(sqlWhere, "  legal_name like ? \n");
				}
				if (StringUtils.isNotEmpty(condition.getLegalCertificatesNo())) {
					DatabaseUtils.appendCondition(sqlWhere, "  legal_certificates_no = ? \n");
				}
				if (condition.getPointsFrom() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  points >= ? \n");
				}
				if (condition.getPointsTo() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  points <= ? \n");
				}
				if (condition.getRank() != null) {
					DatabaseUtils.appendCondition(sqlWhere, "  rank = ? \n");
				}
				DatabaseUtils.appendCondition(sqlWhere, "  delete_flag = false \n");
				if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
					sqlWhere.append("limit ?, ? \n");
				}
			}
			
			if (sqlWhere.length() > 0) {
				sql.append("where \n").append(sqlWhere);
			}
			
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#memberSearch] sql={}", sql.toString());
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			
			if (condition != null) {
				int index = 0;
				if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
					statement.setString(++index, "%" + condition.getMemberLoginName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLicenseNumber())) {
					statement.setString(++index, condition.getLicenseNumber());
				}
				if (StringUtils.isNotEmpty(condition.getEnterpriseName())) {
					statement.setString(++index, "%" + condition.getEnterpriseName() + "%");
				}
				if (condition.getEnterpriseProperty() != null && condition.getEnterpriseProperty() > 0) {
					statement.setInt(++index, condition.getEnterpriseProperty());
				}
				if (condition.getEnterpriseCategory() != null && condition.getEnterpriseCategory() > 0) {
					statement.setInt(++index, condition.getEnterpriseCategory());
				}
				if (StringUtils.isNotEmpty(condition.getContactName())) {
					statement.setString(++index, "%" + condition.getContactName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getContactCertificatesNo())) {
					statement.setString(++index, condition.getContactCertificatesNo());
				}
				if (StringUtils.isNotEmpty(condition.getAreaCode())) {
					statement.setString(++index, "%" + condition.getAreaCode() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getTelephone())) {
					statement.setString(++index, "%" + condition.getTelephone() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getMobile())) {
					statement.setString(++index, "%" + condition.getMobile() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getEmail())) {
					statement.setString(++index, "%" + condition.getEmail() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getQqNo())) {
					statement.setString(++index, "%" + condition.getQqNo() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getWeixinNo())) {
					statement.setString(++index, "%" + condition.getWeixinNo() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLegalName())) {
					statement.setString(++index, "%" + condition.getLegalName() + "%");
				}
				if (StringUtils.isNotEmpty(condition.getLegalCertificatesNo())) {
					statement.setString(++index, "%" + condition.getLegalCertificatesNo() + "%");
				}
				if (condition.getPointsFrom() != null) {
					statement.setInt(++index, condition.getPointsFrom());
				}
				if (condition.getPointsTo() != null) {
					statement.setInt(++index, condition.getPointsTo());
				}
				if (condition.getRank() != null) {
					statement.setInt(++index, condition.getRank());
				}
				
				if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
					statement.setInt(++index, (condition.getPage() - 1) * condition.getRows());
					statement.setInt(++index, condition.getRows());
				}
			}
			
			ResultSet resultSet = statement.executeQuery();
			ResultSetMapper<Member> resultSetMapper = new ResultSetMapper<Member>();
			List<Member> members = resultSetMapper.mapResultSetToList(resultSet, Member.class);
			
			return members;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#memberSearch] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#memberSearch] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public Member showMember(String memberLoginName) {
		if(logger.isInfoEnabled()){
			logger.info("[MemberManageDao_JDBCImpl#showMember] start.");
		}
		PreparedStatement statement=null;
		try{	
			StringBuilder sql = new StringBuilder()
			.append("select \n")
			.append("  member_login_name, \n")
			.append("  member_login_password, \n")
			.append("  license_number, \n")
			.append("  enterprise_name, \n")
			.append("  enterprise_property, \n")
			.append("  enterprise_property_name, \n")
			.append("  enterprise_category, \n")
			.append("  enterprise_category_name, \n")
			.append("  contact_name, \n")
			.append("  contact_certificates_type, \n")
			.append("  contact_certificates_no, \n")
			.append("  area_code, \n")
			.append("  telephone, \n")
			.append("  mobile, \n")
			.append("  mobile_certificated, \n")
			.append("  email, \n")
			.append("  email_certificated, \n")
			.append("  qq_no, \n")
			.append("  weixin_no, \n")
			.append("  legal_name, \n")
			.append("  legal_certificates_type, \n")
			.append("  legal_certificates_no, \n")
			.append("  image_contact_certificates_front, \n")
			.append("  image_contact_certificates_back, \n")
			.append("  image_legal_certificates_front, \n")
			.append("  image_legal_certificates_back, \n")
			.append("  image_license, \n")
			.append("  points, \n")
			.append("  rank, \n")
			.append("  rank_name, \n")
			.append("  create_user, \n")
			.append("  create_date, \n")
			.append("  update_user, \n")
			.append("  update_date, \n")
			.append("  delete_flag \n")			
			.append("from \n")
			.append("  v_member \n")
			.append("where \n")
			.append("  member_login_name = ? \n")
			.append("  and delete_flag = false \n");
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#showMember] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, memberLoginName);
			ResultSet resultSet = statement.executeQuery();			
			ResultSetMapper<Member> resultSetMapper=new ResultSetMapper<Member>();
			Member member=resultSetMapper.mapResultSetToObject(resultSet, Member.class);
			return member;
		}catch(SQLException e){
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#showMember] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#showMember] finish.");
			}
			if (statement != null) { 
				try { 
					statement.close(); 
					} catch (SQLException e) {} 
				}
		}
		
	}

	@Override
	public void deleteMember(String memberLoginName) {
		if(logger.isInfoEnabled()){
			logger.info("[MemberManageDao_JDBCImpl#deleteMember] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("delete from \n")
			.append("  t_member \n")
			.append("where \n")
			.append("  member_login_name = ? \n");
			
			if(logger.isTraceEnabled()){
				logger.trace("[MemberManageDao_JDBCImpl#deleteMember] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, memberLoginName);
			statement.executeUpdate();
			
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[MemberManageDao_JDBCImpl#deleteMember] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#deleteMember] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}
		
	}
	@Override
	public void changePassWord(String memberLoginName, String memberLoginOldPassword, String memberLoginNewPassword) {
		if (logger.isInfoEnabled()) {
			logger.info("[MemberManageDao_JDBCImpl#changePassWord] start.");
		}

		PreparedStatement statement = null;
		try {
			StringBuilder sql = new StringBuilder()
			.append("update \n")
			.append(" t_member \n")
			.append(" set \n")
			.append("  member_login_password = ? ,\n")
			.append("  update_user = ? ,\n")
			.append("  update_date = current_timestamp \n")
			.append("where \n")
			.append("  member_login_name = ? \n")
			.append(" and member_login_password = ? \n");

			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#changePassWord] sql={}", sql);
			}
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, DigestUtils.SHAHashing(memberLoginNewPassword));
			statement.setString(2, memberLoginName);
			statement.setString(3, memberLoginName);
			statement.setString(4, DigestUtils.SHAHashing(memberLoginOldPassword));
			statement.executeUpdate();
		} catch (SQLException | NoSuchAlgorithmException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[MemberManageDao_JDBCImpl#changePassWord] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[MemberManageDao_JDBCImpl#changePassWord] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {

				}
			}
		}
	}

    @Override
    public void changeMemberRank(String memberLoginName, Integer rank, String updateUser) {
        if (logger.isInfoEnabled()) {
            logger.info("[MemberManageDao_JDBCImpl#changeMemberRank] start.");
        }

        PreparedStatement statement = null;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("update t_member \n")
                .append("set \n")
                .append("  rank = ?, \n")
                .append("  update_user = ?, \n")
                .append("  update_date = current_timestamp \n")
                .append("where \n")
                .append("  member_login_name = ? \n");
            
            if (logger.isTraceEnabled()) {
                logger.trace("[MemberManageDao_JDBCImpl#changeMemberRank] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, rank);
            statement.setString(2, updateUser);
            statement.setString(3, memberLoginName);
            
            statement.executeUpdate();
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[MemberManageDao_JDBCImpl#changeMemberRank] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[MemberManageDao_JDBCImpl#changeMemberRank] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }
}
